from sqlalchemy import Table, Column, Integer, String, Float, MetaData
from sqlalchemy import create_engine
from sqlalchemy.sql import select
from sqlalchemy.sql import text
import os,sys,logging
from Db2Connection import Db2Connection
sys.path.append(os.path.join(os.path.split(os.path.realpath(__file__))[0],"../tools"))

__authors__  = '陈灿'
__version__  = '1.0'
__license__  = '东莞农商行'

dataType={"integer":Integer,
          "decimal":Integer,
          "float":Float,
          "varchar":String,
          "char":String,
          "character":String
          }

class Db2Base:
    '''''
    这个类是查找数据库的基类，封装了访问数据库的基本方法。
    '''

    def __init__(self,tableSchema,tableName,engine):
        '''''
        这是基类的初始化方法，通过数据库连接字符串和需要查询的表名进行初始化
        '''
        #conf = configparser.ConfigParser()
        #conf.read(os.path.abspath(os.path.join(os.path.dirname(__file__),"./conf.ini")))
        #print(os.path.abspath(os.path.join(os.path.dirname(__file__),"./conf.ini")))
        #sqlStr = conf.get("db", "dburl")

        self.engine = engine
        self.metadata = MetaData()
        self.table_info = Table('COLUMNS', self.metadata,
        Column('tabschema', String),
        Column('colname', String),
        Column('keyseq', String),
        Column('typename', String),schema="SYSCAT")
        #self.conn=self.engine.connect()

        self.T = Table(tableName, self.metadata)
        s = text(
            "select tabschema,colname,typename,keyseq from SYSCAT.COLUMNS where TABNAME =:tableName and TABSCHEMA=:tableSchema"
        )
        conn = self.engine.connect()
        result = conn.execute(s, tableName=tableName.upper(),tableSchema = tableSchema.upper())
        rows = result.fetchall()
        conn.close()
        if len(rows)==0:
            raise Exception(tableName+"表没有找到")
        self.T.schema = rows[0][0]
        for column in rows:
            if column[3]:
                self.T.append_column(Column(column[1].lower(),dataType.get(column[2].lower(),String),primary_key=True))
            else:
                self.T.append_column(Column(column[1].lower(), dataType.get(column[2].lower(),String)))

    def getT(self,t):
        '''''
        查询方法，根据输入的字典t中的参数，动态生成sql语句进行查询
        '''
        sql = self.__makeWhereSqlScript(t)
        s = select([self.T]).where(text(sql))
        conn = self.engine.connect()
        result = conn.execute(s)
        rows = result.fetchall()
        conn.close()
        list_of_dicts = [dict(row.items()) for row in rows]
        return list_of_dicts

    def getTByCondition(self,sql):
        '''''
        查询方法，根据输入的where条件语句,生成sql语句进行查询
        '''
        s = select([self.T]).where(text(sql))
        conn = self.engine.connect()
        result = conn.execute(s)
        rows = result.fetchall()
        conn.close()
        list_of_dicts = [dict(row.items()) for row in rows]
        return list_of_dicts

    def exec_sql(self,sql):
        conn = self.engine.connect()
        result = conn.execute(text(sql))
        rows = result.fetchall()
        conn.close()
        list_of_dicts = [dict(row.items()) for row in rows]
        return list_of_dicts

    def getTByPrimaryKey(self,t):
        '''''
        查询方法，根据输入的字典t中的参数，生成带有主键的查询语句进行查询
        '''
        sql = self.__makePrimaryKeyWhereSqlScript(t)
        s = select([self.T]).where(text(sql))
        conn = self.engine.connect()
        result = conn.execute(s)
        rows = result.fetchall()
        conn.close()
        list_of_dicts = [dict(row.items()) for row in rows]
        return list_of_dicts


    def updateT(self,t,mode=False):
        '''''
        更新方法，根据输入的字典t中的参数，动态生成sql语句进行更新
        '''
        conn = self.engine.connect()
        sql = self.__makePrimaryKeyWhereSqlScript(t)
        if len(self.getTByPrimaryKey(t))!=0 and mode==False:
            conn.execute(self.T.update().where(text(sql)).values(t))
            conn.close()
            return
        if len(self.getTByPrimaryKey(t))!=0 and mode==True:
            raise Exception ('记录已经存在')
            conn.close()
            return
        if len(self.getTByPrimaryKey(t)) == 0:
            conn.execute(self.T.insert(), t)
            conn.close()
            return

    def insertT(self,t):
        conn = self.engine.connect()
        conn.execute(self.T.insert(), t)
        conn.close()
        return

    def deleteT(self,t):
        '''''
        查询方法，根据输入的字典t中的参数，动态生成带有主键的sql语句进行删除
        '''
        sql = self.__makePrimaryKeyWhereSqlScript(t)
        conn = self.engine.connect()
        conn.execute(self.T.delete().where(sql))
        conn.close()

    def __makePrimaryKeyWhereSqlScript(self,t):
        sqlScript = ""
        list=[]
        for v in self.T.primary_key:
            list.append(v)

        i = 0
        for v in list:
            if i==len(list):
                break
            if i==len(list)-1:
                sqlScript = sqlScript + v.name + "='" + str(t.get(v.name, "")) + "'"
                break
            sqlScript = sqlScript + v.name + "='" + str(t.get(v.name,"")) +"' and "
            i = i + 1
        return sqlScript

#根据字典t中变量
    def __makeWhereSqlScript(self,t):
        sqlScript = ""
        i = 0
        for (k, v) in t.items():
            if len(t.items()) == 0:
                break;
            if i == len(t.items()) - 1:
                sqlScript = sqlScript + k + "='" + str(v) + "'"
                break;
            sqlScript = sqlScript + k + "='" + str(v) + "' and "
            i = i + 1
        return sqlScript



if __name__ == "__main__":
    t5ProdInfo = Db2Base("lego","sys_menu",Db2Connection.getEngine())
    list = t5ProdInfo.getT({})
    for v in list:
        print(v)

